package org.lq.dlz.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.dlz.common.Const.ProductStatusEnum;
import org.lq.dlz.common.Page;
import org.lq.dlz.dao.ProductDao;
import org.lq.dlz.entity.Product;
import org.lq.dlz.query.ProductQuery;
import org.lq.dlz.util.DateUtils;
import org.lq.dlz.util.JDBCUtils;

public class ProductDaoImpl extends JDBCUtils implements ProductDao {

	@Override
	public int addProduct(Product product) {
		String sql = "insert into kb_product(category_id,product_name,main_image,sub_images,detail,price,stock,status,create_time,update_time,dictionary_id) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?)";
		
	try {
		return queryRunner.update(sql,product.getCategory_id(),
				product.getProduct_name(),
				product.getMain_image(),
				product.getSub_images(),
				product.getDetail(),
				product.getPrice(),
				product.getStock(),
				product.getStatus(),
				DateUtils.getTimestamp(),
				DateUtils.getTimestamp(),
				product.getDictionary_id());
	} catch (SQLException e) {
		e.printStackTrace();
	}
		return 0;
	}

	@Override
	public int updateProduct(Product product) {
		String sql = "update kb_product set category_id=?,product_name=?,main_image=?,"
				+ "sub_images=?,detail=?,price=?,stock=?,status=?,create_time=?,"
				+ "update_time=? dictionary_id=? where id=?";
		try {
			return queryRunner.update(sql,product.getCategory_id(),
					product.getProduct_name(),
					product.getMain_image(),
					product.getSub_images(),
					product.getDetail(),
					product.getPrice(),
					product.getStock(),
					product.getStock(),
					product.getStatus(),
					DateUtils.getTimestamp(),
					DateUtils.getTimestamp(),
					product.getDictionary_id(),
					product.getId()
					);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return 0;
	}

	@Override
	public int deleteProduct(int id) {
		String sql = "delete from kb_product where id=?";
			try {
				return queryRunner.update(sql,id);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return 0;
	}

	@Override
	public Product getProductById(int id) {
		String sql = "select * from kb_product where id = ?";
		
		try {
			return queryRunner.query(sql, new BeanHandler<Product>(Product.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		
		
		return null;
	}

	@Override
	public List<Product> findPage(Page<Product> page) {
		String sql  = "select * from kb_product limit ?,?";
		try {
			List<Product > list = queryRunner.query(sql, new BeanListHandler<Product>(Product.class),page.getStartIndex(),page.getPageSize());
			page.setList(list);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public List<Product> getProductByCategoryId(int cid, Page<Product> page) {
		String sql = "select * from kb_product where category_id=? limit ?,?";
		try {

			List<Product> list =  queryRunner.query(sql, new BeanListHandler<Product>(Product.class),cid,page.getStartIndex(),page.getPageSize());

			page.setList(list);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int totalCount() {
		String sql = "select count(1) from kb_product";
		try {
			return queryRunner.query(sql, new ScalarHandler<Long>()).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
/**
 * 多条件查询  拼接SQL语句
 */
	@Override
	public List<Product> findPage(Page<Product> page, ProductQuery query) {
		String sql = "select * from kb_product ";
		sql+=query.toWhereSQL();
		sql+=query.toORderSQL();
		sql+=" limit ?,? ";
		try {
			List<Product> list =  queryRunner.query(sql, new BeanListHandler<Product>(Product.class),page.getStartIndex(),page.getPageSize());
			page.setList(list);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int totalCount(ProductQuery query) {
		String sql = "select count(1) from kb_product ";
		sql+=query.toWhereSQL();
		try {
			return queryRunner.query(sql, new ScalarHandler<Long>()).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int updateStatus(int id, int status) {
		// TODO Auto-generated method stub
		String sql="update kb_product set status=?,update_time=? where id=?";
		try {
		return queryRunner.update(sql,status,DateUtils.getTimestamp(),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	
	}
/**
 * 获取最近修改的商品
 */
	@Override
	public List<Product> getHOTProduct(ProductStatusEnum enum1) {
		String sql = "select * from kb_product where status = ? order by update_time desc";
		try {
			List<Product> list =  queryRunner.query(sql, new BeanListHandler<Product>(Product.class),enum1.getCode());
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void findProductByCid(Page<Product> page, ProductQuery query) {
		String sql ="select * from kb_product where category_id in (" +
				"SELECT id FROM kb_category where id =? or parent_id = ?)";
		sql+= query.toORderSQL();
		
		try {
			List<Product> list = queryRunner.query(sql, new BeanListHandler<Product>(Product.class),query.getCid(),query.getCid());
			
			page.setList(list);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public List<Product> findCartProduct(int... id) {
		StringBuffer sql = new StringBuffer("select * from kb_product where id in (");
		for (int i : id) {
			sql.append(i+",");
		}
		sql.replace(sql.length()-1, sql.length(), ")");
		try {
			return queryRunner.query(sql.toString(), new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateStock(int pid, int stock) {
String sql = "update kb_product set stock=stock-? where id=?";
		
		try {
			return queryRunner.update(sql,stock,pid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stock;
	}

}
